﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using crm_library.DataAccess.TableReps;

namespace crm_library.DataAccess
{
	public static class DB
	{
		public static string ConnectionString { get; set; }


		#region connection
		private static SqlConnection _connection;
		public static SqlConnection Connection
		{
			get
			{
				if (_connection == null) _connection = new SqlConnection(ConnectionString);
				return _connection;
			}
		}
		public static ConnectionState Connectionstate
		{
			get
			{
				return Connection.State;
			}
		}
		#endregion connection

		#region data access

		public static void CreateObject<T>(T itemToInsert) where T : ObjectRepresentation, new()
		{
			var command = itemToInsert.ToCreateCommand();
			try
			{
				command.Connection = Connection;
				command.Connection.Open();
				command.ExecuteNonQuery();
			}
			finally
			{
				command.Connection.Close();
			}
		}

		public static T ReadObject<T>(T destination, int pk) where T : ObjectRepresentation, new()
		{
			var command = destination.ToReadCommand();
			T item = null;
			try
			{
				command.Connection = Connection;
				command.Connection.Open();
				item = Fill(destination, command.ExecuteReader());
			}
			finally
			{
				command.Connection.Close();
			}
			return item;
		}

		public static List<T> ReadObjectList<T>(T destination) where T : ObjectRepresentation, new()
		{
			var command = destination.ToReadAllCommand();
			List<T> list = null;
			try
			{
				command.Connection = Connection;
				command.Connection.Open();
				list = FillList(destination, command.ExecuteReader());
			}
			finally
			{
				command.Connection.Close();
			}
			return list;
		}

		public static void UpdateObject<T>(T itemToUpdate, int pk) where T : ObjectRepresentation, new()
		{

			var command = itemToUpdate.ToUpdateCommand();
			try
			{
				command.Connection = Connection;
				command.Connection.Open();
				command.ExecuteNonQuery();
			}
			finally
			{
				command.Connection.Close();
			}

		}


		public static void DeleteObject<T>(T itemToDelete, int pk) where T : ObjectRepresentation, new()
		{
			var command = itemToDelete.ToDeleteCommand();
			try
			{
				command.Connection = Connection;
				command.Connection.Open();
				command.ExecuteNonQuery();
			}
			finally
			{
				command.Connection.Close();
			}
		}

		#endregion data access

		private static T Fill<T>(T destination, IDataReader source) where T : ObjectRepresentation, new()
		{
			if (source.Read())
			{
				foreach (var property in destination.PropertyInfoList)
				{
					var dbAttributes = (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true);

					if (!dbAttributes.Any()) continue;

					var value = source[dbAttributes[0].ColumnName];
					property.SetValue(destination, source[dbAttributes[0].ColumnName] == DBNull.Value ? null : value, null);
				}

				return destination;
			}
			return null;
		}

		private static List<T> FillList<T>(T objOfType, IDataReader source) where T : ObjectRepresentation, new()
		{
			var list = new List<T>();
			while (source.Read())
			{
				T temp = new T();
				foreach (var property in temp.PropertyInfoList)
				{
					var dbAttributes = (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true);

					if (!dbAttributes.Any()) continue;

					var value = source[dbAttributes[0].ColumnName];
					property.SetValue(temp, source[dbAttributes[0].ColumnName] == DBNull.Value ? null : value, null);
				}

				list.Add(temp);
			}
			source.Close();
			list.OrderBy(item => item.GetPKValue());
			return list;
		}
	}
}

